#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>

// 回调函数，用于处理查询结果
static int callback(void* NotUsed, int argc, char** argv, char** azColName) {
    int i;
    // 遍历每一列的结果并打印
    for (i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int food_write(const char* recommended_dish, const char* user_preferences) {
    sqlite3* db;
    char* zErrMsg = 0;
    int rc;
    char sql[1024];  

    // 打开或创建名为 food.db 的 SQLite 数据库文件
    rc = sqlite3_open("sqlite/food.db", &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }
    else {
        fprintf(stderr, "Opened database successfully\n");
    }


    // 使用 SQLite 的安全格式化函数
    sqlite3_snprintf(sizeof(sql), sql, 
                    "INSERT INTO food(recommended_dish, user_preferences) VALUES('%q', '%q');",
                    recommended_dish, user_preferences);

    // 执行 SQL 语句
    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else {
        fprintf(stdout, "Records created successfully\n");
    }
    sqlite3_close(db);
    return 0;
}


int menu_write(const char* recognition_result, const char* freshness) {
    sqlite3* db;
    char* zErrMsg = 0;
    int rc;
    char sql[1024];  

    // 打开或创建名为 menu.db 的 SQLite 数据库文件
    rc = sqlite3_open("sqlite/menu.db", &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }
    else {
        fprintf(stderr, "Opened database successfully\n");
    }


    // 使用 SQLite 的安全格式化函数
    sqlite3_snprintf(sizeof(sql), sql, 
                    "INSERT INTO menu(recognition_result, freshness) VALUES('%q', '%q');",
                    recognition_result, freshness);

    // 执行 SQL 语句
    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else {
        fprintf(stdout, "Records created successfully\n");
    }
    sqlite3_close(db);
    return 0;
}



int aht20_write(double temperature, double humidity) {
    sqlite3* db;
    char* zErrMsg = 0;
    int rc;
    // 使用足够大的缓冲区
    char sql[1024];  

    rc = sqlite3_open("sqlite/aht20.db", &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);  // 确保在错误时关闭数据库
        return 1;
    }
    

    snprintf(sql, sizeof(sql), 
             "INSERT INTO aht20(temperature, humidity) VALUES(%.2f, %.2f);",
             temperature, humidity);

    rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    } else {
        fprintf(stdout, "Records created successfully\n");
    }
    
    sqlite3_close(db);
    return 0;
}


int sql_read(const char* db_name) {
    sqlite3* db;
    char* zErrMsg = 0;
    int rc;
    const char* sql;

    if (strcmp(db_name, "food") == 0) {
        rc = sqlite3_open("sqlite/food.db", &db);
    }
    else if(strcmp(db_name, "aht20") == 0){
        rc = sqlite3_open("sqlite/aht20.db", &db);
    }
    else if(strcmp(db_name, "menu") == 0){
        rc = sqlite3_open("sqlite/menu.db", &db);
    }
    
    
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }
    else {
        fprintf(stderr, "Opened database successfully\n");
    }

    if (strcmp(db_name, "food") == 0) {
        sql = "SELECT * FROM food ORDER BY time DESC LIMIT 1;";
    }
    else if(strcmp(db_name, "aht20") == 0){
        sql = "SELECT * FROM aht20 ORDER BY time DESC LIMIT 1;";
    }
    else if(strcmp(db_name, "menu") == 0){
        sql = "SELECT * FROM menu ORDER BY time DESC LIMIT 1;";
    }

    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else {
        fprintf(stdout, "Operation done successfully\n");
    }

    sqlite3_close(db);
    return 0;
}


// 新增函数：获取最近一次识别的食材
char* history_food() {
    sqlite3* db;
    sqlite3_stmt* stmt;
    char* result = NULL;
    
    // 打开数据库
    int rc = sqlite3_open("sqlite/menu.db", &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
        return NULL;
    }
    
    // 准备SQL查询 - 获取最新的识别结果
    const char* sql = "SELECT recognition_result FROM menu ORDER BY time DESC LIMIT 1;";
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "准备查询失败: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return NULL;
    }
    
    // 执行查询
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        // 获取结果字符串
        const unsigned char* text = sqlite3_column_text(stmt, 0);
        if (text) {
            // 分配内存并复制结果
            result = strdup((const char*)text);
        }
    } else {
        fprintf(stderr, "未找到记录或查询错误\n");
    }
    
    // 清理资源
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    
    return result; // 调用者负责释放内存
}

//int main() {
    //food_write("辣子鸡", "增重");
    //menu_write("牛肉", "新鲜");
    //aht20_write(25.1, 45.2);
    //sql_read("food");
    //sql_read("aht20");
    //sql_read("menu");
    //char* last_food = history_food();
    //printf("最近一次识别的食材是: %s\n", last_food);
    //return 0;
//}

